﻿using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using OfficeOpenXml;
using System.Windows.Forms;
using Microsoft.VisualBasic.FileIO;
using System;

namespace Serm
{
    /// <summary>
    /// Make Excel Report
    /// </summary>
    public static class ReportMaker
    {
        #region Constructor Overload
        /// <summary>
        /// Set CSV file name.
        /// </summary>
        /// <param name="TemplateFileName">IN Tmplate</param>
        /// <param name="OutFileName">OUT FileName</param>
        /// <param name="CsvFileName">CSV Path</param>
        public static void MakeReport(string TemplateFileName, string OutFileName, string CsvFileName)
        {
            DataTable data = ReadCsvToDataTable(true, CsvFileName);
            Make(TemplateFileName, OutFileName, data);
        }

        /// <summary>
        /// Set datasource
        /// </summary>
        /// <param name="TemplateFileName">IN Tmplate</param>
        /// <param name="OutFileName">OUT FileName</param>
        /// <param name="dt">DataTable</param>
        public static void MakeReport(string TemplateFileName, string OutFileName, DataTable dt)
        {
            Make(TemplateFileName, OutFileName, dt);
        }
        #endregion

        /// <summary>
        /// Insert data into Excel sheet
        /// </summary>
        /// <param name="TemplateFileName">TemplateFileName</param>
        /// <param name="OutFileName">OUT FileName</param>
        /// <param name="Data">OutFileName</param>
        private static void Make(string TemplateFileName, string OutFileName, DataTable Data)
        {
            //copy a sheet from template
            if (File.Exists(OutFileName)) File.Delete(OutFileName);
            ExcelPackage pk = new ExcelPackage(new FileInfo(OutFileName), new FileInfo(TemplateFileName));
            ExcelWorksheet ws = pk.Workbook.Worksheets[1];

            int StartRow = 1; //start row 
            int EndRow;     //end row
            int CountRow;     //rows count
            int PageEndRow;
            int NowPage = 0;

            #region case page type report
            string CellValue1 = (string)ws.Cells[ws.Dimension.End.Row, 1].Value; CellValue1 = CellValue1 ?? "";//null ""
            if (CellValue1 == "")
            {
                MessageBox.Show("Cell 'A" + ws.Dimension.End.Row.ToString() + "' is empty.");
                pk.Dispose();
                return;
            }

            //get the end row of repeat rows
            if (CellValue1.ToLower() == "{end}")
            {
                EndRow = ws.Dimension.End.Row;
                PageEndRow = ws.Dimension.End.Row;
            }
            else
            {
                MessageBox.Show("Input 'End' into Cell 'A" + ws.Dimension.End.Row.ToString() + "' to get the position.");
                pk.Dispose();
                return;
            }
            CountRow = EndRow - StartRow + 1;//calc the row count
            #endregion

            #region case line type report
            for (int i = 1; i < EndRow; i++)
            {
                string tmp = (string)ws.Cells[i, 1].Value; tmp = tmp ?? "";
                if (tmp.ToLower() == "{repeatrow}")
                {
                    StartRow = i;
                    EndRow = i;
                    CountRow = 1;
                    ws.Cells[i, 1].Value = ""; // clear the mark
                    break;
                }
            }
            #endregion

            ws.Cells[ws.Dimension.End.Row, 1].Value = ""; // clear the mark

            #region Set data into cell
            for (int i = 0; i < Data.Rows.Count; i++)
            {
                //copy the first row set.
                string FromStart = StartRow.ToString();
                string FromEnd = EndRow.ToString();
                string ToStart = (StartRow + CountRow * i).ToString();
                string ToEnd = (EndRow + CountRow * i).ToString();

                ws.Cells[FromStart + ":" + FromEnd].Copy(ws.Cells[ToStart + ":" + ToEnd]);

                //set data
                for (int j = 0; j < Data.Columns.Count; j++)
                {
                    foreach (ExcelComment c in ws.Comments)
                    {
                        if (c.Text.ToLower().Replace("\r", "").Replace("\n", "") == Data.Columns[j].Caption.ToLower().Replace("\r", "").Replace("\n", ""))
                        {
                            ws.Cells[c.Address].Offset(CountRow * i, 0).Value = Data.Rows[i][j];//first offset is 0.
                        }
                    }
                }

                if ((EndRow + CountRow * i) == (PageEndRow + CountRow * i) || (i+1) % (PageEndRow - EndRow + 1) == 0)
                {
                    ExcelRow newRow = ws.Row((EndRow + CountRow * i));//insert a pagebreak
                    newRow.PageBreak = true;
                    NowPage++;
                }
            }

            //at last delete all cell's comment.
            while (ws.Comments.Count > 0)
            {
                ws.Comments.RemoveAt(0); 
            }
            #endregion

            pk.Save();
            pk.Dispose();
        }

        /// <summary>
        /// convert csv to DataTable
        /// </summary>
        /// <param name="hasHeader">Header Yes No</param>
        /// <param name="fileName">Filename</param>
        /// <returns>DataTable</returns>
        private static DataTable ReadCsvToDataTable(bool hasHeader, string fileName)
        {
            DataTable dt = new DataTable();

            TextFieldParser parser = new TextFieldParser(fileName, Encoding.GetEncoding("UTF-8"));
            parser.TextFieldType = FieldType.Delimited;

            parser.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited; 
            parser.SetDelimiters(",");
            parser.HasFieldsEnclosedInQuotes = true;                                 
            parser.TrimWhiteSpace = true;                                           

            string[] data;

            if (!parser.EndOfData)
            {
                data = parser.ReadFields();
                int cols = data.Length;
                if (hasHeader)
                {
                    for (int i = 0; i < cols; i++)
                    {
                        dt.Columns.Add(new DataColumn(data[i]));
                    }
                }
                else
                {
                    for (int i = 0; i < cols; i++)
                    {
                        dt.Columns.Add(new DataColumn());
                    }

                    DataRow row = dt.NewRow();
                    for (int i = 0; i < cols; i++)
                    {
                        row[i] = data[i];
                    }
                    dt.Rows.Add(row);
                }
            }

            while (!parser.EndOfData)
            {
                data = parser.ReadFields();
                DataRow row = dt.NewRow();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row[i] = data[i];
                }
                dt.Rows.Add(row);
            }
            return dt;
        }
    
        //Memo
        //namespace OfficeOpenXml.Drawing.Vml.ExcelVmlDrawingComment
        //public string Address
        //{
        //    get
        //    {
        //        return Range.Address;
        //    }
        //}
    }

}
